package kk.dao.impl;

import kk.dao.UserDao;
import kk.domain.User;
import kk.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class UserDaoImpl implements UserDao {
    private JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());

    /**
     * 查询所有用户
     * @return
     */
    @Override
    public List<User> findAll() {
        String sql="select * from user";
        List<User> users = template.query(sql, new BeanPropertyRowMapper<User>(User.class));
        return users;
    }

    /**
     * 添加用户
     * @param user
     */
    @Override
    public void add(User user) {
        String sql="insert into user values( null,?,?,?,?,?,? )";
        template.update(sql,user.getName(),user.getGender(),user.getAge(),user.getAddress(),user.getQq(),user.getEmail());
    }

    /**
     * 根据id删除用户
     * @param id
     */
    @Override
    public void deleteUserById(int id) {
        String sql = "delete from user where id = ?";
        template.update(sql, id);
    }

    /**
     * 根据id查找用户
     * @param id
     * @return
     */
    @Override
    public User findUserById(int id) {
        String sql="select * from user where id = ?";
        try {
            User user = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
            return user;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 更改用户
     * @param user
     */
    @Override
    public void updateUser(User user) {
        String sql="update user set name = ? , gender = ? , age = ? , address = ? ,qq = ? ,email = ? where id = ?";
        template.update(sql,user.getName(),user.getGender(),user.getAge(),user.getAddress(),user.getQq(),user.getEmail(),user.getId());
    }

    /**
     * 查询总记录数(分页，模糊查询)
     * @return
     * @param condition
     */
    @Override
    public int findTotalCount(Map<String, String[]> condition) {
        String sql=" select count(*) from user where 1 = 1 ";
        StringBuilder sb=new StringBuilder(sql);
        //遍历condition
        Set<String> keySet = condition.keySet();
        //定义参数的集合
        List param =new ArrayList();
        for (String key : keySet) {
            //排除currentPage和rows
            if ("currentPage".equals(key) || "rows".equals(key)){
                continue;
            }
            //获取value
            String value = condition.get(key)[0];
            if (value!=null && !"".equals(value)){
                sb.append(" and "+key+" like ? ");
                param.add("%"+value+"%");
            }
        }
        sql=sb.toString();
        //用于测试
        int totalCount = template.queryForObject(sql, Integer.class,param.toArray());
        return totalCount;
    }

    /**
     * 根据条件分页查询用户
     * @param start
     * @param rows
     * @param condition
     * @return
     */
    @Override
    public List<User> findUserByPage(int start, int rows, Map<String, String[]> condition) {
        String sql=" select * from user where 1 = 1 ";
        StringBuilder sb=new StringBuilder(sql);
        //遍历condition
        Set<String> keySet = condition.keySet();
        //定义参数的集合
        List param =new ArrayList();
        for (String key : keySet) {
            //排除currentPage和rows
            if ("currentPage".equals(key) || "rows".equals(key)){
                continue;
            }
            //获取value
            String value = condition.get(key)[0];
            if (value!=null && !"".equals(value)){
                sb.append(" and "+key+" like ? ");
                param.add("%"+value+"%");
            }
        }
        //添加分页查询
        sb.append(" limit ? , ? ");
        param.add(start);
        param.add(rows);
        sql=sb.toString();

        try {
            List<User> list = template.query(sql, new BeanPropertyRowMapper<User>(User.class),param.toArray());
            return list;
        }catch (Exception e){
            return null;
        }
    }
}
